#!perl  -I../scripts/lib

use Spreadsheet::ParseExcel;
use Product;
use DBI;
use Run;
use Connect;
use Log;
use English;

use File::Basename;
use FileHandle; # autoflush



sub cmdline_error
        {
        my ($message) = @_;
        warn "$message\n" if (defined $message);
        print "Error command\n";
        exit;
        }

my ($book_n,$book_p,$db,$user,$pw)=@ARGV;
unless (($book_n) and ($book_p) and ($db) and ($user) and ($pw) )
{
cmdline_error ("usage:/data/tools/bin/perl test.pl <location of product_tree> <location of tracking_sheet> <database> <user> <pw>");
}
my $dbh = Connect::db_con("$db","$user","$pw");


my @format =();
my @report =();
my @error =();
my @error_1 =();
my $product=new Product;
($product)=$product->excel($book_n,"PRODUCT");


($product)=$product->excel($book_p,"COMM");


my $oBook =$product->{'COMM'}{'oBook'};
my @product_list=();
my @field_pr=("CommercialName","FamillyName","FriendlyName2");
my @field_cm=("Commercialreference","SIMLOCK");
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
	{
	my  $oWkS = $oBook->{Worksheet}[$iSheet];
	$product->{'COMM'}{'SHEET'} = $iSheet;
	my $product_ex= $oWkS->{Name};
	if ($product_ex =~ /^Elle/i)
	{
	$product_ex=~ s/\D//g;
	$product_ex=~ s/0//g;
	$product_ex= "ELLE N".$product_ex;
	}
	elsif ($product_ex =~ /History/i)
	{
	next;
	}
	elsif ($product_ex =~ /Message/i)
	{
	next;
	}
	elsif ($product_ex =~ /Welcome/i)
        {
        next;
        }
	elsif ($product_ex =~ /End/i)
        {
        next;
        }

	else
	{
	$product_ex=~ s/\D//g;
	}
	
	($product)=$product->getpd($product_ex,"PRODUCT",@field_pr);
	if (! defined $product->{$product_ex}{'FamillyName'})
	{
	push @error_1,"sheet name cannot be recognise:$oWkS->{Name}";
	next;
	}

	my $sql_stmnt="SELECT id FROM product WHERE commercial_name=\"$product->{$product_ex}{'CommercialName'}\"";
        my ($sth,$result) = Run::ex($sql_stmnt,$dbh);
        my @product_id= $sth->fetchrow_array;
        Run::finish($sth);
        if(@product_id)
        	{
	        $product->{$product_ex}{'ID'}=shift @product_id;
		}
        else
        	{
	        my $sql_stmnt_fa="SELECT id FROM product_family WHERE name=\"$product->{$product_ex}{'FamillyName'}\"";
        	my ($sth_fa,$result_fa) = Run::ex($sql_stmnt_fa,$dbh);
	        my @family_id= $sth_fa->fetchrow_array;
        	Run::finish($sth_fa);
		if (@family_id)
		        {
		        $product->{$product_ex}{'FamillyID'}=shift @family_id;
		        }
		else
        		{
			my $sql_insert_fa= "INSERT INTO product_family (name) VALUE (\"$product->{$product_ex}{'FamillyName'}\")";
			($sth,$result) = Run::ex($sql_insert_fa,$dbh);
			if ($result ne "1")
                        {
                        push @error, "ERROR:error to insert $sql_insert_fa";
			next;
                        }
			else
			{
			Run::finish($sth);
			my $sql_stmnt_fa = "SELECT id FROM product_family WHERE name=\"$product->{$product_ex}{'FamillyName'}\"";
			($sth,$result) = Run::ex($sql_stmnt_fa,$dbh);
			my @family_id= $sth->fetchrow_array;
			$product->{$product_ex}{'Familly ID'}=shift @family_id;
			Run::finish($sth);
			}
			}
			my $sql_insert = "INSERT INTO product (product_family_id,name,commercial_name) VALUE ($product->{$product_ex}{'FamillyID'},\"$product->{$product_ex}{'FriendlyName2'}\",\"$product->{$product_ex}{'CommercialName'}\")";
		        ($sth,$result) = Run::ex($sql_insert,$dbh);
			if ($result ne "1")
			{
                        push @error, "ERROR:error to insert $sql_insert";
                        next;
			}
		Run::finish($sth);
		
		my $sql_stmnt = "SELECT id FROM product WHERE commercial_name=\"$product->{$product_ex}{'CommercialName'}\"";
		($sth,$result) = Run::ex($sql_stmnt,$dbh);
		my @product_id= $sth->fetchrow_array;
		Run::finish($sth);
	        $product->{$product_ex}{'ID'}=shift @product_id;
        	}
	push @product_list,$product_ex;
		
		($product)=$product->getpd($product_ex,"COMM",@field_cm);
		 


		my $row = $product->{'COMM'}{'IR'};
		my $line = 0;
		while (my $oWKC = $oWkS->{Cells}[$row][$line])
			{
			my $value_field = $oWKC->Value;
			if (($value_field =~ /\n/) or ($value_field =~ /\s/))
				{
				$value_field =~ s/\n//g;
				$value_field =~ s/\s//g;
				}

			if ($value_field =~ /PersoFile/i)
				{
				$product->{'PersoFile'}{'IC'} = $line;
				}
				elsif ($value_field =~ /SWpackage/i)
				{
				$product->{'SWpackage'}{'IC'} = $line;
				}
				$line ++;
			}

		$row = $row+1;
		while (my $oWkC = $oWkS->{Cells}[$row][$product->{'Commercialreference'}{'IC'}])
        		{
			my $value_comm = $oWkC->Value;
			if ($value_comm)
			{
			$value_comm=~ s/\s//g;
		        $product->{$value_comm}{'Commercialreference'}=$value_comm;
        		my $row_a = $row;
		        while ((! $oWkS->{Cells}[$row_a][$product->{'SIMLOCK'}{'IC'}]->Value) and ($row_a > $product->{'COMM'}{'IR'}))
			        {
			        $row_a--;
			        }
			$product->{$value_comm}{'SIMLOCK'}=$oWkS->{Cells}[$row_a][$product->{'SIMLOCK'}{'IC'}]->Value;
			if ($product->{$value_comm}{'SIMLOCK'} eq "")
				{
				push @report,"SIMLOCK key is empty:sheet:$oWkS->{Name},ref:$product->{$value_comm}{'Commercialreference'}";
				}
		        if (defined $product->{'PersoFile'}{'IC'})
        			{
				$row_a = $row;
			        while ((! $oWkS->{Cells}[$row_a][$product->{'PersoFile'}{'IC'}]->Value) and ($row_a > $product->{'COMM'}{'IR'}))
				        {
				        $row_a--;
				        }
			        my $value = $oWkS->{Cells}[$row_a][$product->{'PersoFile'}{'IC'}]->Value;
			        if (($value =~ /\n/) or ($value=~ /\s/))
				{
				$value =~ s/\n//g;
				$value =~ s/\s//g;
				}
				if ($value =~ /\S\S\S\S\S\S\S\S\S\S\S\S\S\S\S\S/)
				{
                                $value =~ s/^\S\S\S\S\S\S\S\S//;
                                }
				else
				{
				my $re="sheet:$oWkS->{Name},ref:$product->{$value_comm}{'Commercialreference'},perso file:$value";
				push @format,"perso file's format error:$re";
				}

			        $product->{$value_comm}{'PersoFile'}=$value;
			        }
		        elsif (defined $product->{'SWpackage'}{'IC'})
			        {
			        $row_a = $row;
			        while ((! $oWkS->{Cells}[$row_a][$product->{'SWpackage'}{'IC'}]->Value) and ($row_a > $product->{'COMM'}{'IR'}))
				        {
				        $row_a--;
				        }
				my $nospace=$oWkS->{Cells}[$row_a][$product->{'SWpackage'}{'IC'}]->Value;
				$nospace=~ s/\s//g;
				$nospace=~ s/(.*)exe(.*)/$1exe/;

			        $product->{$value_comm}{'SWpackage'}=$nospace;
			        }
			my $refname = $product->{$value_comm}{'Commercialreference'};
			if ($refname =~ /\[/)
			{
			$refname =~ s/(.*)-\[(.*)/$1/;
			$refname =~ s/(.*)\[(.*)/$1/;
			my $refname_tmp=$product->{$value_comm}{'Commercialreference'};
			$product->{$value_comm}{'Commercialreference'} = $refname;
			$sql_stmnt="UPDATE commercial_ref SET ref=\"$product->{$value_comm}{'Commercialreference'}\" WHERE ref=\"$refname_tmp\"";
                        ($sth,$result) = Run::ex($sql_stmnt,$dbh);
                        Run::finish($sth);

			}
			$refname=~ m/(.*-\d)(\S)(.*)/;
			if ( $2 ne "\*")
			{
			$product->{$value_comm}{'Commercialreference'} = "$1"."\*"."$3";
			}

			$sql_stmnt="SELECT simlock,product_id,mldc_file FROM commercial_ref WHERE ref=\"$product->{$value_comm}{'Commercialreference'}\"";
	                ($sth,$result) = Run::ex($sql_stmnt,$dbh);
        	        @ref= $sth->fetchrow_array;
                	Run::finish($sth);
			if (@ref)
				{
				my ($simlock_db,$pdid_db,$addinfo_db)=@ref;
				if (! defined $addinfo_db)
				{
				$addinfo_db="NULL";
				}
				if (($simlock_db eq $product->{$value_comm}{'SIMLOCK'}) and ($pdid_db == $product->{$product_ex}{'ID'}) and (($addinfo_db eq $product->{$value_comm}{'SWpackage'}) or ($addinfo_db eq $product->{$value_comm}{'PersoFile'})))
					{
					$row ++;
					next;
					}
				else
                                        {
					my $re = "sheet:$oWkS->{Name},\tref:$product->{$value_comm}{'Commercialreference'},\tsimlock:$product->{$value_comm}{'SIMLOCK'}\($simlock_db\),\tproduct_id:$product->{$product_ex}{'ID'}\($pdid_db\),\t$addinfo:$product->{$value_comm}{'PersoFile'}/$product->{$value_comm}{'SWpackage'}\($addinfo_db\)";
                                        push @report,"commerical_ref keys are diff:$re";
                                        if ($simlock_db eq "")
                                               	{
						$sql_stmnt="UPDATE commercial_ref SET simlock=\"$product->{$value_comm}{'SIMLOCK'}\" WHERE ref=\"$product->{$value_comm}{'Commercialreference'}\"";
						($sth,$result) = Run::ex($sql_stmnt,$dbh);
						Run::finish($sth);
						}
					if ($pdid_db == 0)
                                               	{
	                                        $sql_stmnt="UPDATE commercial_ref SET product_id=$product->{$product_ex}{'ID'} WHERE ref=\"$product->{$value_comm}{'Commercialreference'}\"";
                                              	($sth,$result) = Run::ex($sql_stmnt,$dbh);
	                                        Run::finish($sth);
        	                                }
					if (($addinfo_db eq "") or ($addinfo_db eq "NULL"))
						{
						if (defined $product->{$value_comm}{'SWpackage'})
		                                       	{
                			           	$sql_stmnt="UPDATE commercial_ref SET mldc_file=\"$product->{$value_comm}{'SWpackage'}\" WHERE ref=\"$product->{$value_comm}{'Commercialreference'}\"";
                                        		}
				                elsif (defined $product->{$value_comm}{'PersoFile'})
        	                	                {
                	                		$sql_stmnt="UPDATE commercial_ref SET mldc_file=\"$product->{$value_comm}{'PersoFile'}\" WHERE ref=\"$product->{$value_comm}{'Commercialreference'}\"";
                        	               		}
			        	        ($sth,$result) = Run::ex($sql_stmnt,$dbh);
                        		        if ($result ne "1")
                                        	{
			                               	push @error, "ERROR:error to update $sql_stmnt";
				                        $row ++;
                			                next;
                                        	}
			                        Run::finish($sth);
						}
					}
				}
			else
				{
				if (defined $product->{$value_comm}{'SWpackage'})
					{
					$sql_stmnt_insert="INSERT INTO commercial_ref (ref,simlock,product_id,mldc_file) VALUE (\"$product->{$value_comm}{'Commercialreference'}\",\"$product->{$value_comm}{'SIMLOCK'}\",$product->{$product_ex}{'ID'},\"$product->{$value_comm}{'SWpackage'}\")";
					}
				elsif (defined $product->{$value_comm}{'PersoFile'})
					{
					$sql_stmnt_insert="INSERT INTO commercial_ref (ref,simlock,product_id,mldc_file) VALUE (\"$product->{$value_comm}{'Commercialreference'}\",\"$product->{$value_comm}{'SIMLOCK'}\",$product->{$product_ex}{'ID'},\"$product->{$value_comm}{'PersoFile'}\")";
					}
				($sth,$result) = Run::ex($sql_stmnt_insert,$dbh);
				if ($result ne "1")
                	       		{
		                        push @error, "ERROR:error to insert $sql_stmnt_insert";
					$row ++;
					next;
                		        }
      			        Run::finish($sth);
					
				}

        		}
			$row ++;
			}
        
		
        }

{
Log::print("report",@report);
}

if (@error != "")
{
Log::print("error",@error);
}

if (@error_1 != "")
{
Log::print("error",@error_1);
}


if (@format != "")
{
Log::print("format",@format);
}

Log::print("end");

my $logfile=Log::log_file;

my $filename = basename($logfile);
my $exlname = basename ($book_p);
my $logname = $logfile;
$logfile=~ s/$filename//;

my $name="$logfile"."$exlname"."_$filename";
rename ("$logname","$name");

print "Log file saved at $name\n";
Log::send_mail($name);
exit(0);
